package com.yinhaochuan.result;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSQL {

	public static Statement setStatement(String database) {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/" + database;
		String user = "root";
		String password = "123456";
		Statement st = null;
		try {
			Class.forName(driver);
			Connection conn = DriverManager.getConnection(url, user, password);
			if (!conn.isClosed()) {
				System.out.println("Succeeded connectiong to the Database :"
						+ database);
			}
			st = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return st;
	}

	public static String createTableName(int queryID) {
		String table = "query_";
		String _name = "";
		if (queryID < 10) {
			_name = "00" + queryID;

		} else {
			_name = "0" + queryID;
		}
		String tablename = table + _name;
		return tablename;
	}

	public static String createTableName(int queryID, String part, String prefix) {
		String table = prefix + "-" + part + "_";
		String _name = "";
		if (queryID < 10) {
			_name = "00" + queryID;

		} else {
			_name = "0" + queryID;
		}
		String tablename = table + _name;
		return tablename;
	}

	public static void printDiffBeDB(String[] database) throws SQLException {
		for (int i = 0; i < database.length; i++) {
			System.out.println(database[i]);
			Statement st = setStatement(database[i]);
			for (int j = 1; j <= 50; j++) {
				ResultSet rs = null;
				String tablename = createTableName(j);
				String sql = "select *,count(*) as cnt from " + tablename;
				rs = st.executeQuery(sql);
				int cnt = 0;
				if (rs.first()) {
					cnt = rs.getInt("cnt");
				}
				if (cnt != 10000) {
					System.out.print(j + "(" + cnt + ")" + "\t");
				}
			}
			System.out.println();
		}
	}

	public static void printDiffBePre(String database, String part,
			String prefix) throws SQLException {
		Statement st = setStatement(database);
		System.out.println(prefix);
		for (int j = 1; j <= 50; j++) {
			ResultSet rs = null;
			String tablename = createTableName(j, part, prefix);
			String sql = "select *,count(*) as cnt from " + tablename;
			rs = st.executeQuery(sql);
			int cnt = 0;
			if (rs.first()) {
				cnt = rs.getInt("cnt");
			}
			if (cnt != 10000) {
				System.out.print(j + "(" + cnt + ")" + "\t");
			}
		}
		System.out.println();
	}

	public static void main(String[] args) throws SQLException {
		String database = "fi";
		String[] part = { "title", "desc" };
		for (int i = 0; i < part.length; i++) {
			printDiffBePre(database, part[i], "");
			printDiffBePre(database, part[i], "order");
		}

		database = "sd";
		for (int i = 0; i < part.length; i++) {
			printDiffBePre(database, part[i], "unorder");
		}
	}
}
